﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Windows.Forms;
using Tzx.Utilities.Npoi;

namespace Tzx.Tools.Payroll2Images.Exe.ExcelToTable
{
    /// <summary>
    /// 将Excel导入到程序,存储到DataTable
    /// </summary>
    public partial class UcExcelToTable : UserControl
    {
        /// <summary>
        /// 构造
        /// </summary>
        public UcExcelToTable()
        {
            InitializeComponent();

            Load += UcExcelImport_Load;
            BtnSelectExcel.Click += BtnSelectExcel_Click;
            TxtExcelFile.TextChanged += TxtExcelFile_TextChanged;

            BtnReload.Click += BtnReload_Click;
            BtnOpenFolder.Click += BtnOpenFolder_Click;

            //下一步,触发事件
            BtnNext.Click += delegate (object sender, EventArgs e)
            {
                string msg;
                if (!ChangeConfigLinkColumnName(out msg))
                {
                    MessageBox.Show(msg,"关联列",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
                    return;
                }

                EventHandler handler = NextClick;
                if (handler != null) { handler(this, EventArgs.Empty); }
            };

            //

        }

        /// <summary>
        /// 说明文字
        /// </summary>
        private const string TEXT_Read_Excel =
            "从Excel文件第一个工作表（Sheet1）的 A1 单元格读取表格，" +
            "第一行为标题行，第二行开始为数据行，不要使用合并单元格，不需要任何单元格格式，只要数值。" +
            "";

        /// <summary>
        /// 弹出框标题
        /// </summary>
        private string Title
        {
            get { return ParentForm != null ? ParentForm.Text : string.Empty; }
        }

        #region public 返回数据表

        /// <summary>
        /// 数据表
        /// </summary>
        public DataTable ExcelTable
        {
            get
            {
                return _excelTable;
            }
        }
        private DataTable _excelTable;

        /// <summary>
        /// 下一步
        /// </summary>
        public event EventHandler NextClick;

        #endregion

        #region 事件方法

        /// <summary>
        /// 窗体加载时
        /// </summary>
        private void UcExcelImport_Load(object sender, EventArgs e)
        {
            LblReadText.Text = TEXT_Read_Excel;

            //datagridview样式
            dataGridView1.EnableHeadersVisualStyles = false; //样式颜色有效
            
            //列标题不换行
            dataGridView1.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False;

            //设定包括Header和所有单元格的列宽自动调整
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

            //设定不包括Header所有单元格的行高自动调整
            //AllCells;设定包括Header和所有单元格的行高自动调整
            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders;
        }

        /// <summary>
        /// 选定Excel文件变化时,加载Excel文件
        /// </summary>
        private void TxtExcelFile_TextChanged(object sender, EventArgs e)
        {
            LoadExcelFileName();//加载Excel数据到内存DataTable
        }

        /// <summary>
        /// 重新加载Excel文件
        /// </summary>        
        private void BtnReload_Click(object sender, EventArgs e)
        {
            LoadExcelFileName();//加载Excel数据到内存DataTable
        }

        /// <summary>
        /// 打开文件夹,定位文件
        /// </summary>        
        private void BtnOpenFolder_Click(object sender, EventArgs e)
        {
            string file = TxtExcelFile.Text = TxtExcelFile.Text.Trim();
            if (file.Length == 0)
            {
                return;
            }
            if (!File.Exists(file))
            {
                MessageBox.Show("文件不存在",Title,MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
                return;
            }

            //
            System.Diagnostics.Process.Start("Explorer", "/select," + file);
        }

        /// <summary>
        /// 选择文件按钮
        /// </summary>
        private void BtnSelectExcel_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog ofd = new OpenFileDialog())
            {
                ofd.Title = "选择Excel文件";
                ofd.Filter = "Excel文件(*.xlsx,*.xls)|*.xlsx;*.xls";
                ofd.InitialDirectory = Application.StartupPath;
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    TxtExcelFile.Text = ofd.FileName;
                }
            }//using

        }
        
        #endregion

        #region private

        /// <summary>
        /// 加载Excel文件
        /// </summary>
        private void LoadExcelFileName()
        {
            string file = TxtExcelFile.Text = TxtExcelFile.Text.Trim();
            if (file.Length <= 0)
            {
                return;
            }
            bool ok = LoadExcel(file);
            if (!ok)
            {
                return;
            }

            //显示
            dataGridView1.DataSource = ExcelTable;

            //列名关联选择
            FillColumnNameToSelect();
        }

        /// <summary>
        /// 加载Excel文件
        /// </summary>
        /// <param name="excelFile"></param>
        private bool LoadExcel(string excelFile)
        {
            if (!File.Exists(excelFile))
            {
                MessageBox.Show("Excel文件不存在", App.Title, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return false;
            }

            //是xls还是xlsx
            bool isExcel2007 = excelFile.EndsWith(".xlsx", true, null);
            bool isExcel97 = excelFile.EndsWith(".xls", true, null);
            if (!isExcel2007 && !isExcel97)
            {
                MessageBox.Show("不是支持的Excel文件(*.xls或*.xlsx)", App.Title, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return false;
            }

            //
            bool ok = false;
            using (FileStream fs = new FileStream(excelFile, FileMode.Open, FileAccess.Read))
            {
                NpoiImport import = new NpoiImport(fs, isExcel2007);
                _excelTable = import.ExcelStreamToDataTable(0, 0); //保存到成员
                fs.Close();
                ok = true;
            }

            #region x
            //添加2字段
            //if (_excelTable != null)
            //{
            //    //添加字段:表必需要有一个唯一行字段
            //    if (!_excelTable.Columns.Contains(App.ColumnFixName_FID))
            //    {
            //        _excelTable.Columns.Add(App.ColumnFixName_FID, typeof(int));
            //    }
            //    //添加字段:表必需要有一个接收人名称字符串字段
            //    if (!_excelTable.Columns.Contains(App.ColumnFixName_FEmployeeAllName))
            //    {
            //        _excelTable.Columns.Add(App.ColumnFixName_FEmployeeAllName, typeof(string));
            //    }
            //    int rowcount = _excelTable.Rows.Count;
            //    for (int idx = 0; idx < rowcount; idx++)
            //    {
            //        //唯一列
            //        _excelTable.Rows[idx][App.ColumnFixName_FID] = (idx + 1);
            //        //接收人
            //        string cnname = _excelTable.Rows[idx][App.ColumnName_Import_CnName].ToString();
            //        string enname = _excelTable.Rows[idx][App.ColumnName_Import_EnName].ToString();
            //        string number = _excelTable.Rows[idx][App.ColumnName_Import_JobNumber].ToString();
            //        _excelTable.Rows[idx][App.ColumnFixName_FEmployeeAllName] = string.Format("{0}{1}{2}", cnname, enname, number);
            //    }
            //}
            #endregion

            //返回
            return ok;
        }

        /// <summary>
        /// 关联列表
        /// </summary>
        private string[] LinkColumnNameSS =
        {
            "选择列(" + App.ColumnName_Import_CnName + ")",    //中文名
            "选择列(" + App.ColumnName_Import_EnName + ")",    //英文名
            "选择列(" + App.ColumnName_Import_JobNumber + ")", //工号
        };

        /// <summary>
        /// 填充关联列名,并选中默认项
        /// </summary>
        private void FillColumnNameToSelect()
        {
            //
            Cbb_CNName.Items.Clear();
            Cbb_ENName.Items.Clear();
            Cbb_Number.Items.Clear();

            Cbb_CNName.Items.Add(LinkColumnNameSS[0]);  //中文名
            Cbb_ENName.Items.Add(LinkColumnNameSS[1]);  //英文名
            Cbb_Number.Items.Add(LinkColumnNameSS[2]);  //工号

            //
            if (ExcelTable == null) return;

            //
            foreach (DataColumn col in ExcelTable.Columns)
            {
                Cbb_CNName.Items.Add(string.Format("{0}", col.ColumnName));
                Cbb_ENName.Items.Add(string.Format("{0}", col.ColumnName));
                Cbb_Number.Items.Add(string.Format("{0}", col.ColumnName));
            }

            //
            int idx_1 = Cbb_CNName.Items.IndexOf(App.ColumnName_Import_CnName);
            if (idx_1 > -1)
            {
                Cbb_CNName.SelectedIndex = idx_1;
            }
            int idx_2 = Cbb_CNName.Items.IndexOf(App.ColumnName_Import_EnName);
            if (idx_2 > -1)
            {
                Cbb_ENName.SelectedIndex = idx_2;
            }
            int idx_3 = Cbb_CNName.Items.IndexOf(App.ColumnName_Import_JobNumber);
            if (idx_3 > -1)
            {
                Cbb_Number.SelectedIndex = idx_3;
            }

        }

        /// <summary>
        /// 更新配置导入表关联列
        /// </summary>
        private bool ChangeConfigLinkColumnName(out string message)
        {
            //
            message = null;

            //
            string s1 = Cbb_CNName.SelectedItem.ToString();
            if (Array.IndexOf(LinkColumnNameSS, s1) > -1)
            {
                message = string.Format("需关联列名(" + s1 + ")");
                return false;
            }

            string s2 = Cbb_ENName.SelectedItem.ToString();
            if (Array.IndexOf(LinkColumnNameSS, s2) > -1)
            {
                message = string.Format("需关联列名(" + s2 + ")");
                return false;
            }

            string s3 = Cbb_Number.SelectedItem.ToString();
            if (Array.IndexOf(LinkColumnNameSS, s3) > -1)
            {
                message = string.Format("需关联列名(" + s3 + ")");
                return false;
            }

            //
            App.ColumnName_Import_CnName = s1;
            App.ColumnName_Import_EnName = s2;
            App.ColumnName_Import_JobNumber = s3;

            return true;
        }

        #endregion

    }
}